package com.wh.wisdomsite.xbox.server.dbCompare; /**
 * Created by jinshaowei on 14/8/29.
 *
 * <p>Description: 此类用于分析客户库之间,或者开发库与客户库之间出现的不一致问题!
 *                 要比较两个库中的数据之间差异
 *
 *                 修改成所需批对的数据库即可。
 *           注意:  在整个处理过程中,表和视图完全按照同一概念处理.</p>
 */

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

public class CompareCertainDB {


    /**
     * 说明：此方法用于比较两个不同库中是否有相同表名,相同字段名的字段中字段大小不一致的情况
     *      如果有,则返回一个集合类用于保存查询结果.
     *      其中,每条信息都保存在一个内部类 IntityClass 的属性 T_NAME , C_NAME ,
     *                                                    C_SIZE , CC_SIZE 中.
     * @return
     * @throws Exception
     */
    public List<CompareResult> compareColumnLength(String dbLink, String owner,String owner2, Connection conn) throws Exception{
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            List<CompareResult> compColumnLengthArray=new ArrayList<CompareResult>();
            StringBuffer strSQL=new StringBuffer();
            strSQL.append("select a.atable_name,a.acolumn_name,a.acolumn_type,a.adata_length,b.bdata_length, a.adata_precision, b.bdata_precision, a.adata_scale, b.bdata_scale, a.achar_length, b.bchar_length from ");
            strSQL.append(" (");
            strSQL.append("     select table_name atable_name, column_name acolumn_name,data_type acolumn_type,data_length adata_length, data_precision adata_precision, data_scale adata_scale, char_length achar_length ");
            strSQL.append("      from all_tab_columns where owner='"+ owner +"'");
            strSQL.append(" )a,");
            strSQL.append(" (");
            strSQL.append("     select table_name btable_name, column_name bcolumn_name,data_type bcolumn_type,data_length bdata_length,data_precision bdata_precision, data_scale bdata_scale, char_length bchar_length ");
            strSQL.append("      from all_tab_columns@"+ dbLink +" where owner='"+ owner2 +"'");
            strSQL.append(" )b ");
            strSQL.append("where a.acolumn_name=b.bcolumn_name and ");
            strSQL.append("      a.atable_name=b.btable_name and");
            strSQL.append("( ( (a.adata_length<>b.bdata_length or a.adata_precision <> b.bdata_precision or a.adata_scale <> b.bdata_scale or " +
                    "(a.adata_precision is null and b.bdata_precision is not null) or (a.adata_precision is not null and b.bdata_precision is null) or " +
                    "(a.adata_scale is null and b.bdata_scale is not null) or (a.adata_scale is not null and b.bdata_scale is null)) " +
                    "and (a.acolumn_type = 'NUMBER' AND B.bcolumn_type = 'NUMBER')) or(" +
                    "a.achar_length  <> b.bchar_length and (a.acolumn_type = 'VARCHAR2' AND b.bcolumn_type = 'VARCHAR2')))");
            ps=conn.prepareStatement(strSQL.toString());
            rs=ps.executeQuery();
            while(rs.next()){
                String tableName; //表名
                String columnName; //字段名称
                String columnType;
                Integer columnSize; //字段大小
                Integer compColumnSize; //被比对字段大小
                Integer columnPrecision;//字段精度
                Integer compColumnPrecision;//被比对字段精度
                Integer columnScale;//小数位
                Integer compColumnScale;//被比对字段小数位
                Integer charLength;
                Integer compCharLength;
                tableName=rs.getString("atable_name");
                columnName=rs.getString("acolumn_name");
                columnType=rs.getString("acolumn_type");
                columnSize=rs.getInt("adata_length");
                compColumnSize=rs.getInt("bdata_length");
                columnPrecision=rs.getInt("adata_precision");
                compColumnPrecision=rs.getInt("bdata_precision");
                columnScale=rs.getInt("adata_scale");
                compColumnScale=rs.getInt("bdata_scale");
                charLength=rs.getInt("achar_length");
                compCharLength=rs.getInt("bchar_length");
                CompareResult result=new CompareResult(tableName,columnName,columnType,columnSize,compColumnSize,
                        columnPrecision, compColumnPrecision, columnScale, compColumnScale, charLength, compCharLength);
                compColumnLengthArray.add(result);
            }
            return compColumnLengthArray;
        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
            throw new Exception(e);
        } finally{
            DBConnection.close(rs);
            DBConnection.close(ps);
            DBConnection.freeConn(conn);
        }
    }
    /**
     * 说明：此方法用于比对在两个库同一名称的表中,字段名称在一个库中存在,而在另一个库中不存在的情况
     * @param owner 数据库账号
     * @param dbLink 对比的数据库连接
     * @return
     * @throws Exception
     */
    public List<CompareResult> compColsInOneNotInOther(String dbLink, String owner,String owner2, Connection conn) throws Exception{
        List<CompareResult> diffColsList=new ArrayList<CompareResult>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            StringBuffer strSQL=new StringBuffer();

            strSQL.append("select table_name,column_name,data_type from ");
            strSQL.append("    (");
            strSQL.append("        select table_name,column_name,data_type, (table_name||column_name) table_column_name from all_tab_columns where owner='"+ owner +"'");
            strSQL.append("        and table_name in (select distinct(table_name) btablename from all_tab_columns@"+ dbLink +" where owner ='"+ owner2 +"')");
            strSQL.append("    )B ");
            strSQL.append(" where B.table_column_name not in (select (table_name||column_name) table_column_name from all_tab_columns@"+ dbLink +" where owner ='"+ owner2 +"')");
            ps=conn.prepareStatement(strSQL.toString());
            rs=ps.executeQuery();
            while(rs.next()){
                String tableName; //表名
                String columnName; //字段名称
                String columnType;
                tableName=rs.getString("table_name");
                columnName=rs.getString("column_name");
                columnType=rs.getString("data_type");
                CompareResult result=new CompareResult(tableName,columnName,columnType);
                diffColsList.add(result);
            }
            return diffColsList;
        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
            throw new Exception(e);
        } finally{
            DBConnection.close(rs);
            DBConnection.close(ps);
            DBConnection.freeConn(conn);
        }
    }


    /***
     * 比对字段注释
     * @param dbLink
     * @param owner
     * @param conn
     * @return
     * @throws Exception
     */
    public List<CompareResult> compColComments(String dbLink, String owner,String owner2, Connection conn) throws Exception{

        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            List<CompareResult> compColCommentsList=new ArrayList<CompareResult>();
            StringBuffer strSQL=new StringBuffer();

            //查找相同的表名
            List<String> comTabLi=new ArrayList<String>();
            strSQL.append("select atablename from ");
            strSQL.append("    ( ");
            strSQL.append("        select distinct(table_name) atablename from all_tab_columns where owner='"+ owner +"'");
            strSQL.append("    )A ");
            strSQL.append("where A.atablename in (select distinct(table_name) btablename from all_tab_columns@"+ dbLink +" where owner ='"+ owner2 +"')");
            ps=conn.prepareStatement(strSQL.toString());
            rs=ps.executeQuery();
            String comTab;
            while(rs.next()){
                comTab=rs.getString("atablename");
                comTabLi.add(comTab);
            }

            Iterator iterInBoth=comTabLi.iterator();
            String tabinBoth;
            strSQL.setLength(0);
            while(iterInBoth.hasNext()){
                tabinBoth=(String)iterInBoth.next();
                //循环查找不同的字段注释

                strSQL.append("select a.table_name atable_name, a.column_name acolumn_name,a.comments acomments,b.comments bcomments from ");
                strSQL.append(" (");
                strSQL.append("      select * from user_col_comments where table_name = '"+ tabinBoth +"'");
                strSQL.append(" )a,");
                strSQL.append(" (");
                strSQL.append("      select * from user_col_comments@"+ dbLink +" where table_name='"+ tabinBoth +"'");
                strSQL.append(" )b ");
                strSQL.append(" where a.column_name = b.column_name and (a.comments <> b.comments or (a.comments is null and b.comments is not null) or a.comments is not null and b.comments is null)");
                ps=conn.prepareStatement(strSQL.toString());
                rs=ps.executeQuery();
                while(rs.next()){
                    String tableName; //表名
                    String columnName; //字段名称
                    String columnComments;//字段注释
                    String compColumnComments;//被比对字段的注释
                    tableName=rs.getString("atable_name");
                    columnName=rs.getString("acolumn_name");
                    columnComments=rs.getString("acomments");
                    compColumnComments=rs.getString("bcomments");
                    CompareResult result=new CompareResult(tableName,columnName,columnComments,compColumnComments);
                    compColCommentsList.add(result);
                }

                strSQL.setLength(0);

            }
            return compColCommentsList;
        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
            throw new Exception(e);
        } finally{
            DBConnection.close(rs);
            DBConnection.close(ps);
            DBConnection.freeConn(conn);
        }
    }



    /**
     * 说明：此方法用于比较并返回两个库中属于一个库,但又不属于另一个库的表信息
     * @param dbLink
     * @param owner
     *
     * @return
     * @throws Exception
     */
    public List<String> tabShldDel(String dbLink, String owner,String owner2, Connection conn) throws Exception{

        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            List<String> dutyTabLi=new ArrayList<String>();
            StringBuffer strSQL=new StringBuffer();

            strSQL.append("select atablename from ");
            strSQL.append("    (");
            strSQL.append("        select distinct(table_name) atablename from all_tab_columns where owner like '%"+owner+"%'");
            strSQL.append("    )A ");
            strSQL.append(" where A.atablename not in ( ");
            strSQL.append("select distinct(table_name) btablename from all_tab_columns@"+ dbLink +" where owner ='"+owner2+"'");
            strSQL.append(" ) ");
            ps=conn.prepareStatement(strSQL.toString());
            rs=ps.executeQuery();
            while(rs.next()){
                String tableName=rs.getString("atablename");
                dutyTabLi.add(tableName);
            }
            return dutyTabLi;
        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
            throw new Exception(e);
        } finally{
            DBConnection.close(rs);
            DBConnection.close(ps);
            DBConnection.freeConn(conn);
        }
    }
    /**
     * 说明：用于返回两个库中的交集表信息
     * @return
     * @throws Exception
     */
    public List<String> commonTabLi(String dbLink, String owner,String owner2, Connection conn) throws Exception{
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            List<String> comTabLi=new ArrayList<String>();
            StringBuffer strSQL=new StringBuffer();
            strSQL.append("select atablename from ");
            strSQL.append("    ( ");
            strSQL.append("        select distinct(table_name) atablename from all_tab_columns where owner='"+ owner +"'");
            strSQL.append("    )A ");
            strSQL.append("where A.atablename in (select distinct(table_name) btablename from all_tab_columns@"+ dbLink +" where owner ='"+ owner2 +"')");
            ps=conn.prepareStatement(strSQL.toString());
            rs=ps.executeQuery();
            String comTab;
            while(rs.next()){
                comTab=rs.getString("atablename");
                comTabLi.add(comTab);
            }
            return comTabLi;
        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
            throw new Exception(e);
        } finally{
            DBConnection.close(rs);
            DBConnection.close(ps);
            DBConnection.freeConn(conn);
        }
    }
    /**
     * 说明:程序处理入口
     * @param args
     */
    public static void main2(String[] args){

        String dbLink = "HAF_QAS1_152_LINK";
        String owner = "HAF";

        String ip = "10.248.58.101";

        try{
            CompareCertainDB compCertDB=new CompareCertainDB();
            System.out.println("==================================================");
            System.out.println("          以下为101中存在但在152中不存在的表        ");
            System.out.println("==================================================");
            List<String> notInTablesList=compCertDB.tabShldDel(dbLink, owner, owner, DBConnection.getConn(ip, owner));
            Iterator iterNotIn=notInTablesList.iterator();
            String notInTable;
            while(iterNotIn.hasNext()){
                notInTable=(String)iterNotIn.next();
                System.out.println(""+notInTable);
            }
            System.out.println("==================================================\n\n\n\n");
            System.out.println("==================================================");


            System.out.println("          以下为101中存在且在152中也存在的表        ");
            System.out.println("==================================================");
            Collection inBothDB=compCertDB.commonTabLi(dbLink, owner, owner, DBConnection.getConn(ip, owner));
            Iterator iter_in_both=inBothDB.iterator();
            String tabinBoth;
            while(iter_in_both.hasNext()){
                tabinBoth=(String)iter_in_both.next();
                System.out.println(""+tabinBoth);
            }
            System.out.println("==================================================\n\n\n\n");
            System.out.println("        开始准备比较两个库中共有表的字段名称.....        \n\n\n\n\n\n\n\n");
            System.out.println("==================================================");
            System.out.println("       以下为101中存在但在152中不存在的字段信息      ");
            System.out.println("==================================================");
            List<CompareResult> columnsNotInList=compCertDB.compColsInOneNotInOther(dbLink, owner, owner, DBConnection.getConn(ip, owner));
            Iterator columnNotInIter=columnsNotInList.iterator();
            CompareResult columnNotInResult;
            while(columnNotInIter.hasNext()){
                columnNotInResult=(CompareResult)columnNotInIter.next();
                System.out.println(""+columnNotInResult.getTableName()+"      "+columnNotInResult.getColumnType()+"      "+columnNotInResult.getColumnName());
            }
            System.out.println("==================================================\n\n\n\n");
            System.out.println("==================================================");

            System.out.println("          以下为152和101大小不一致的字段信息        ");
            System.out.println("==================================================");
            List<CompareResult> columnSizeList=compCertDB.compareColumnLength(dbLink, owner,owner, DBConnection.getConn(ip, owner));
            Iterator columnSizeIter=columnSizeList.iterator();
            CompareResult columnSizeResult;
            System.out.println("\t表名\t字段名\t字段大小101\t被比对字段大小152\t字段精度101\t被比对字段精度152\t小数位长度101\t被比对字段小数位长度152");
            while(columnSizeIter.hasNext()){
                columnSizeResult=(CompareResult)columnSizeIter.next();
                System.out.println("\t"+columnSizeResult.getTableName()+"\t"+columnSizeResult.getColumnName()+"\t"+columnSizeResult.getColumnSize()+"\t"+columnSizeResult.getCompColumnSize()
                        +"\t"+columnSizeResult.getColumnPrecision()+"\t"+columnSizeResult.getCompColumnPrecision()+"\t"+columnSizeResult.getColumnSize()+"\t"+columnSizeResult.getCompColumnScale());
            }
            System.out.println("=====================================================\n\n\n\n");



            System.out.println("          以下为152和101字段注释不一致的字段信息        ");
            System.out.println("==================================================");
            List<CompareResult> commentsDifferent=compCertDB.compColComments(dbLink, owner,owner, DBConnection.getConn(ip, owner));
            Iterator commentsIter=commentsDifferent.iterator();
            CompareResult commentsResult;
            System.out.println("\t表名\t字段名\t字段注释101\t被比对字段注释152");
            while(commentsIter.hasNext()){
                commentsResult=(CompareResult)commentsIter.next();
                System.out.println("\t"+commentsResult.getTableName()+"\t"+commentsResult.getColumnName()+"\t"+commentsResult.getColumnComments()+"\t"+commentsResult.getCompColumnComments());
            }
            System.out.println("=====================================================\n\n\n\n");


        } catch(Exception e){
            e.printStackTrace();
            e.getMessage();
        }
    }


    public static void main(String [] args) throws Exception{

        String dbLink = "HAF_QAS1_152_LINK";
        String owner = "HAF";

        String ip = "10.248.58.101";

        CompareCertainDB compCertDB=new CompareCertainDB();
        //不存在的表名集合
        List/*<String>*/ tableNotInList=compCertDB.tabShldDel(dbLink, owner, owner, DBConnection.getConn(ip, owner));
        //表明存在，但是字段不存在的字段集合
        List/*<CompareResult>*/ columnsNotInList=compCertDB.compColsInOneNotInOther(dbLink, owner,owner, DBConnection.getConn(ip, owner));
        //字段长度不同的集合
        List/*<CompareResult>*/ columnSizeDiffList=compCertDB.compareColumnLength(dbLink, owner,owner, DBConnection.getConn(ip, owner));
        //字段注释不同的集合
        List commentsDiffList=compCertDB.compColComments(dbLink, owner,owner, DBConnection.getConn(ip, owner));


        JSONObject object = new JSONObject();
        JSONArray tableNotIn = new JSONArray(tableNotInList);
        JSONArray columnsNotIn = new JSONArray(columnsNotInList);

    }

}
